package com.example.dao;

import com.example.entity.*;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface InitMapper {
    @Select("select * from ftos.user where u_account = #{account} and u_password = #{password}")
    User getUser(@Param("account") String account, @Param("password") String password);
    @Select("select u_password from ftos.user where u_account = #{account}")
    String getHashedPasswordByAccount(String account);
    @Select("select * from ftos.manager where m_account = #{m_account} and m_password = #{m_password}")
    Manager getManager(@Param("m_account") String m_account, @Param("m_password") String m_password);
    @Select("SELECT city FROM ftos.city ORDER BY a_point_num DESC LIMIT 4")
    List<String> getTop4ArrivalCities();
    @Select("SELECT * FROM ftos.city ORDER BY d_point_num DESC LIMIT 4")
    List<String> getTop4DepartureCities();
    @Select("SELECT * FROM ftos.flight WHERE departure_city = #{departure} AND arrival_city = #{destination} AND departure_date = #{departureDate} ORDER BY economy_price LIMIT 1")
    Flight searchCheapestFlight(@Param("departure") String departure, @Param("destination") String destination, @Param("departureDate") String departureDate);
    //初始化
    //今天的航班
    @Select("select f.* from ftos.flight f join ftos.flight_status fs on f.f_id = fs.f_id " +
            "where departure_date = #{departure_date} and fs.status not in('已延误','已取消','已到达')")
    List<Flight> getFlightList(@Param("departure_date") String departure_date);
    @Select("select * from ftos.flight_status where f_id = #{f_id}")
    FlightStatus getFlightStatusById(int f_id);
    @Update("update ftos.flight_status set status = #{status} where f_id = #{f_id}")
    int updateFlightStatus(@Param("status") String status, @Param("f_id") int f_id);
    @Select("select * from ftos.ticket where status = '即将起飞'")
    List<Ticket> getTicketList();
    @Update("update ftos.ticket set statusText = '已完成',status = 'completed' where id = #{t_id}")
    int updateTicketStatus(int t_id);
    @Select("select o.* from ftos.`order` o join ftos.flight f on f.f_id = o.flightNo " +
            "where o.status = '已支付' and f.departure_date = #{departure_date}")
    List<Order> getOrderList(String departure_date);
    @Select("select * from ftos.flight where f_id = #{f_id}")
    Flight getFlightById(int f_id);
    @Update("update ftos.`order` set status = 'completed', statusText = '已完成' where id = #{o_id}")
    int updateOrderStatus(int o_id);
    @Select("select * from ftos.`order` where statusText = '待支付'")
    List<Order> getUnpaidOrderList();
    @Delete("delete from ftos.`order` where id = #{o_id}")
    int deleteOrder(int o_id);
    @Delete("delete from ftos.ticket where order_id = #{o_id}")
    int deleteTicket(int o_id);
    @Select("select * from ftos.ticket where order_id = #{o_id}")
    List<Ticket> getTicketListByOrderId(int o_id);
    @Update("update ftos.flight_status set economy_space = economy_space + 1 where f_id = #{f_id}")
    int addEconomySpace(int f_id);
    @Update("update ftos.flight_status set business_space = business_space + 1 where f_id = #{f_id}")
    int addBusinessSpace(int f_id);
    @Update("update ftos.flight_status set first_space = first_space + 1 where f_id = #{f_id}")
    int addFirstSpace(int f_id);
    @Update("update ftos.passenger set mileage = mileage + #{mileage} where passenger_id = #{passenger_id}")
    int updateMileage(int passenger_id, double mileage);
    @Select("select * from ftos.passenger where passenger_id = #{passenger_id}")
    Passenger getPassengerById(int passenger_id);
    @Select("select count(*) from ftos.user where ID_type = #{ID_type} and ID_card = #{ID_card}")
    int isUserExist(String ID_type, String ID_card);
    @Update("update ftos.user set grade = #{grade} where ID_type = #{ID_type} and ID_card = #{ID_card}")
    int updateGrade(@Param("grade") String grade,@Param("ID_type") String ID_type,@Param("ID_card") String ID_card);
}
